Public Class clsPedidosSD
    Inherits clsBaseDatos

    Private cInventario As New clsInventarioSD

#Region "Lecturas"
    Public Function cargaNombreDivisa(ByVal pDivisa As Integer) As String
        Dim sql As String

        sql = "Select IsNull(Max(divNombre), '') NombreDivisa " _
            & "From Divisas " _
            & "Where divFolio = " & pDivisa

        Return MyBase.GetDataRow(sql)(0)
    End Function

    Public Function cargaTipoCambio(ByVal pDivisa As Integer) As DataRow
        Dim sql As String

        sql = "usp_TipoDeCambio " & pDivisa

        Return MyBase.GetDataRow(sql)
    End Function

    Public Function cargaAsesor(ByVal pCliente As Integer) As String
        Dim sql As String
        Dim dr As DataRow

        Try
            sql = "Select Clientes.emlFolio, emlNombre " _
                & "From Clientes INNER JOIN Empleados ON Clientes.emlFolio = Empleados.emlFolio " _
                & "Where cliFolio = " & pCliente

            dr = MyBase.GetDataRow(sql)

            Return dr(0) & "|" & dr(1)

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return ""
        End Try
    End Function

    Public Function cargaDatosAsesor(ByVal fEmpleado As Integer) As String
        Dim sql As String
        Dim dr As DataRow

        Try
            sql = "Select emlFolio, emlNombre " _
                & "From Empleados " _
                & "Where emlFolio = " & fEmpleado

            dr = MyBase.GetDataRow(sql)

            If dr Is Nothing Then
                Return ""
            Else
                Return dr(0) & "|" & dr(1)
            End If

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return ""
        End Try
    End Function

    Public Function cargaPrecioXListaXArticuloSD(ByVal articulo As String, _
                                                 ByVal listaPrecios As String) As DataSet
        Dim sql As String
        Try
            sql = "SELECT ISNULL(LPD.lpdPrecio1,0) AS Men,ISNULL(LPD.lpdPrecio2,0) AS MedMay,ISNULL(LPD.lpdPrecio3,0) AS May" _
             & " FROM ListaPrecios_Detalle LPD " _
             & " INNER JOIN Articulos A ON A.artFolio = LPD.artFolio " _
             & " WHERE lspFolio = " & listaPrecios & " AND LPD.artFolio =  " & articulo
            Return MyBase.TraerDataSet(sql)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Public Function cargaPadre(Optional ByVal NoPedido As Integer = 0) As DataSet
        Dim ds As DataSet
        Dim sql As String

        sql = "Select * " _
            & "From Pedidos " _
            & "Where pedFolio = " & NoPedido
        ds = MyBase.TraerDataSet(sql)

        Return ds
    End Function

    Public Function cargaPartidas(Optional ByVal NoPedido As Integer = 0) As DataSet
        Dim ds As DataSet
        Dim sql As String

        sql = "Select * " _
            & "From vPartidas " _
            & "Where pedFolio = " & NoPedido & " " _
            & "Order By pdtFolio"
        ds = MyBase.TraerDataSet(sql)

        Return ds
    End Function

    Public Function lee_umeFolio(ByVal Abreviado As String) As Integer
        Dim sql As String

        Try
            sql = "Select umeFolio " _
                & "From UnidadesMedida " _
                & "Where umeAbreviado = '" & Abreviado & "'"
            Return MyBase.GetDataRow(sql)(0)
        Catch ex As Exception
            MsgBox(ex.Message)
            Return 0
        End Try
    End Function

    Public Function esPosibleCambio(ByVal Articulo As String, _
                                    ByVal NuevoPrecio As Double) As Boolean
        Dim sql As String, Resultado As Boolean = False

        Try
            sql = "Select artPrecio_Compra " _
                & "From articulos " _
                & "Where artClave = '" & Articulo & "'"

            Resultado = (MyBase.GetDataRow(sql)(0) <= NuevoPrecio)

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Return Resultado
    End Function

    Public Function existeReferencia(ByVal Cliente As Integer, _
                                     ByVal Referencia As String) As Boolean
        Dim sql As String, Resultado As Integer

        Try
            sql = "Select Count(*) " _
                & "From Pedidos " _
                & "Where cliFolio = " & Cliente & " And ltrim(pedReferencia) = '" & Referencia & "'"
            Resultado = MyBase.GetDataRow(sql)(0)

            Return Not Resultado = 0
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, ex.TargetSite.Name)
            Return False
        End Try
    End Function

    Public Function traeTipoDocumento(ByVal pCliente As Integer) As String
        Dim sql As String

        Try
            sql = "Select cDocumento Clave " _
                & "From Clientes " _
                & "Where cliFolio = " & pCliente

            Return MyBase.GetDataRow(sql)(0)

        Catch ex As Exception
            MsgBox(ex.Message)

            Return ""
        End Try
    End Function

    Public Function traeTipoEntrega(ByVal Cliente As Integer) As Integer
        Dim sql As String

        Try
            sql = "Select teeFolio " _
                & "From Clientes " _
                & "Where cliFolio = " & Cliente

            Return MyBase.GetDataRow(sql)(0)

        Catch ex As Exception
            MsgBox(ex.Message)

            Return 0
        End Try
    End Function
#End Region

#Region "Movimientos de Datos"
    Public Function guardaPadre(ByVal pFolio As Integer, _
                                ByVal pemlFolio As Integer, _
                                ByVal pTevFolio As Integer, _
                                ByVal pCliente As Integer, _
                                ByVal pFecha As Date, _
                                ByVal pMontoTotal As Double, _
                                ByRef pFolioFiscal As String, _
                                ByVal pImpreso As Boolean, _
                                ByVal pEsPedido As Boolean, _
                                ByVal pReferencia As String, _
                                ByVal Observaciones As String, _
                                ByVal TipoEntrega As Integer, _
                                ByVal pTipoDocumento As String, _
                                ByVal pTipoCambio As Integer, _
                                ByVal pStatus As Boolean) As Integer
        Dim sql As String
        Dim pAConsignacion As Integer
        Dim ds As DataSet

        Try
            sql = "Select cast(cliEsConsignacion as int) " _
                & "From clientes " _
                & "Where cliFolio = " & pCliente
            pAConsignacion = MyBase.GetDataRow(sql)(0)

            If pFolio > 0 Then
                sql = "Update Pedidos " _
                    & "Set emlFolio = " & pemlFolio & ", tevFolio = " & pTevFolio & ", " _
                        & "cliFolio = " & pCliente & ", pedFecha = '" & Format(pFecha, "yyyyMMdd hh:mm:ss") & "', " _
                        & "pedMontoTotal = " & pMontoTotal & ", pedFolioFiscal = '" & pFolioFiscal & "', " _
                        & "pedImpreso = " & IIf(pImpreso, "1", "0") & ", pedEsPedido = " & IIf(pEsPedido, "1", "0") & ", " _
                        & "pedReferencia = '" & pReferencia.Trim & "', pedObservaciones = '" & Observaciones & "', " _
                        & "teeFolio = " & TipoEntrega & ", pedAConsignacion = " & pAConsignacion & ", " _
                        & "cTipoDocumento = '" & pTipoDocumento & "', ticFolio = " & pTipoCambio & ", " _
                        & "Status = " & IIf(pStatus, "1", "0") & " " _
                    & "Where pedFolio = " & pFolio
                MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Edicion)

                Return pFolio
            Else
                Dim Encabezado As String
                Dim FolioActual As Integer

                sql = "Select parValor " _
                    & "From Parametros " _
                    & "Where parNombre = 'EncCotizacion'"
                Encabezado = MyBase.GetDataRow(sql)(0)

                sql = "Select parValor " _
                    & "From Parametros " _
                    & "Where parNombre = 'FolioCotizacion'"
                FolioActual = Val(MyBase.GetDataRow(sql)(0))

                sql = "Update Parametros " _
                    & "Set parValor = " & FolioActual + 1 _
                    & " Where parNombre = 'FolioCotizacion'"
                MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Edicion)

                pFolioFiscal = Encabezado & Format(FolioActual, "000000")

                sql = "Insert into Pedidos " _
                        & "(emlFolio, tevFolio, cliFolio, pedFecha, " _
                        & "pedMontoTotal, pedFolioFiscal, pedImpreso, " _
                        & "pedEsPedido, pedReferencia, pedObservaciones, " _
                        & "teeFolio, pedAConsignacion, cTipoDocumento, ticFolio, Status) " _
                    & "Values (" _
                        & pemlFolio & ", " & pTevFolio & ", " & pCliente & ", getdate(), " _
                        & pMontoTotal & ", '" & pFolioFiscal & "', " & IIf(pImpreso, "1", "0") & ", " _
                        & IIf(pEsPedido, "1", "0") & ", '" & pReferencia.Trim & "', '" & Observaciones.Trim & "', " _
                        & TipoEntrega & ", " & pAConsignacion & ", '" & pTipoDocumento & "', " & pTipoCambio & ", " _
                        & IIf(pStatus, "1", "0") & ")"
                MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Insercion)

                sql = "Select max(pedFolio) " _
                    & "From Pedidos"
                ds = MyBase.TraerDataSet(sql)

                Return ds.Tables(0).Rows(0)(0)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Public Function guardaDetalle(ByVal pFolio As Integer, _
                                  ByVal pFolioPadre As Integer, _
                                  ByVal partFolio As Integer, _
                                  ByVal pCantidad As Double, _
                                  ByVal pumefolio As Integer, _
                                  ByVal pPrecioUnitario As Double, _
                                  ByVal pIVA As Double, _
                                  ByVal pIEPS As Double, _
                                  ByVal pStatus As Boolean) As Integer
        Dim sql As String
        Dim ds As DataSet

        Try
            If pFolio > 0 Then
                sql = "Update Pedidos_Detalle " _
                    & "Set pedFolio = " & pFolioPadre & ", artFolio = " & partFolio & ", " _
                        & "pdtCantidad = " & pCantidad & ", umeFolio = " & pumefolio & ", " _
                        & "pdtpreciounitario = " & pPrecioUnitario & ", pdtIVA = " & pIVA & ", " _
                        & "pdtIEPS = " & pIEPS & ", Status = " & IIf(pStatus, "1", "0") _
                    & "Where pdtFolio = " & pFolio
                MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Edicion)

                Return pFolio
            Else
                sql = "Insert into Pedidos_Detalle " _
                        & "(pedFolio, artFolio, pdtCantidad, umeFolio, pdtpreciounitario, pdtIVA, pdtIEPS, Status) Values (" _
                        & pFolioPadre & ", " & partFolio & ", " & pCantidad & ", " & pumefolio _
                        & ", " & pPrecioUnitario & ", " & pIVA & ", " & pIEPS & ", " & IIf(pStatus, "1", "0") & ")"
                MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Insercion)

                sql = "Select max(pdtFolio) from Pedidos_Detalle"
                ds = MyBase.TraerDataSet(sql)

                Return ds.Tables(0).Rows(0)(0)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Public Sub eliminaPartidas(ByVal FPadre As Integer, _
                               ByVal PartidasExistentes As String)
        Dim sql As String

        Try
            sql = "Delete From Pedidos_Detalle " _
                & "Where pedFolio = " & FPadre & " and pdtFolio not in (" & PartidasExistentes & ")"
            MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Borrado)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Function actualizaAPedido(ByVal pFolio As Integer) As Boolean
        Dim sql As String
        Dim ds As DataSet, dr As DataRow

        Try
            sql = "Select pedEsPedido " _
                & "From Pedidos " _
                & "Where pedFolio = " & pFolio

            If MyBase.GetDataRow(sql)(0) = False Then
                sql = "Select count(*) " _
                    & "From Pedidos " _
                    & "Where pedFolio = " & pFolio & " and pedEsPedido = 1"
                ds = MyBase.TraerDataSet(sql)

                If ds.Tables(0).Rows(0)(0) = 0 Then
                    sql = "Update Pedidos " _
                        & "Set pedEsPedido = 1 " _
                        & "Where pedFolio = " & pFolio
                    MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Edicion)

                    sql = "Select pdtFolio, pdtCantidad " _
                        & "From Pedidos_Detalle " _
                        & "Where pedFolio = " & pFolio
                    ds = MyBase.TraerDataSet(sql)

                    For Each dr In ds.Tables(0).Rows
                        cInventario.InsertaMonitorPartida(dr("pdtFolio"), dr("pdtCantidad"))
                    Next
                End If

                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function cancelaPedido(ByVal pFolio As Integer) As Boolean
        Dim sql As String
        Dim ds As DataSet, dr As DataRow

        Try
            sql = "Select pedEsPedido From Pedidos Where pedFolio = " & pFolio

            If MyBase.GetDataRow(sql)(0) = False Then
                sql = "Select count(*) " _
                    & "From Pedidos " _
                    & "Where pedFolio = " & pFolio & " and pedEsPedido = " & True

                If MyBase.GetDataRow(sql)(0) > 0 Then
                    sql = "Update Pedidos " _
                        & "Set Status = " & False & " " _
                        & "Where pedFolio = " & pFolio
                    MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Edicion)

                    sql = "Select * " _
                        & "From Pedidos_Detalle " _
                        & "Where pedFolio = " & pFolio
                    ds = MyBase.TraerDataSet(sql)

                    For Each dr In ds.Tables(0).Rows
                        cInventario.ActualizaGuiaTrabajo(dr("artFolio"), dr("pedFolio"), dr("pdtCantidad"), clsInventarioSD.LosIdentificadoresTrabajo.Pedidos, clsInventarioSD.LasPosiciones.Original, clsInventarioSD.LasPosiciones.Cancelada)
                    Next
                End If

                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function guardaCambioPrecio(ByVal PrecioUnitario, ByVal PrecioOriginal, ByVal artFolio, ByVal EmpleadoCP, ByVal FolioD)
        Dim sql As String, Costo As Double

        Try
            sql = "Select artPrecio_Compra From Articulos where artFolio = " & artFolio
            Costo = MyBase.GetDataRow(sql)(0)

            sql = "Insert into CambiosDePrecio (emlFolio, cprPrecioReal, cprPrecioModificado, cprCostoCorriente, cprFecha, pdtFolio) Values (" _
                & EmpleadoCP & ", " & PrecioOriginal & ", " & PrecioUnitario & ", " & Costo & ", getdate(), " & FolioD & ")"
            MyBase.MovRegistro(sql, clsBaseDatos.TipoMovimiento.Insercion)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
#End Region
End Class
